package com.itqianfeng;

import com.itqianfeng.mapper.CustomersMapping;
import com.itqianfeng.mapper.impl.CustomerMappingImpl;
import com.itqianfeng.pojo.Customer;
import com.mysql.cj.jdbc.Driver;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 本次练习：<br/>
 * 熟悉jdbc操作数据库的步骤<br/>
 * 1. 注册驱动    2. 获取连接  3. 打包。编译（预编译）  4. 执行SQL语句  5. 获取结果  6. 关闭资源 <br/>
 * 用jdbc知识， 完成对数据库的增删查改<br/>
 * 明白SQL注入的原理，并且学会其解决技巧
 *
 */

public class Main {

    //创建连接需要的参数
    // url 一般由四个部分，
    // 协议(jdbc:mysql:// | 域名:端口(localhost:3306)
    // 路径 sql_store | 参数 ?characterEncoding = utf-8
    private static String url = "jdbc:mysql://localhost:3306/sql_store";
    private static String user = "root";
    private static String password = "da1234";

    // 1. 加载驱动，jdk现在实现自动装载，jdbc3不需要可以自动加载
    // Class.forName("com.mysql.cj.jdbc.Driver");

    // 初始化
    static Connection con = null;
    static Statement st = null;
    static ResultSet resultSet = null;


    static final CustomersMapping map = new CustomerMappingImpl();


    public static void main(String[] args) throws Exception {

        //查询所有数据和通过id查找数据
        // findAll().forEach(System.out::println);
        //
        // System.out.println("============================");
        //
        // System.out.println(findById(1));

        //模拟登录，SQL注入
        // boolean flag =  checkLogin("John", "Rod");
        // //判断是否匹配成功
        // System.out.println(flag);

        // SQL注入，'or '1' = '1,如果没有预编译，这个会查询到任意一个
        // boolean flag = checkLoginPlus("Babara", "MacCaffrey");
        // System.out.println(flag);


        //更改数据
        // Customer customer = findById(1);
        //
        // System.out.println(findById(1));
        // updateTest(customer);
        // System.out.println(findById(1));

        //添加(插入)数据
        // Customer customer = new Customer();
        // // customer.setId();
        // customer.setFirstName("NiguLasi");
        // customer.setLastName("zhaosi");
        // customer.setBirthDate(new Date(120,9,0));
        // customer.setPhone("120");
        // customer.setAddress("南极");
        // customer.setCity("南极星城");
        // customer.setState("AB");
        // customer.setPoint(11111);
        //
        // insertData(customer);

        //delete删除数据 通过id
        int id = 11;
        deleteData(id);


    }

    private static void deleteData(int id) throws SQLException {

        String sql = "delete from customers where customer_id = " + id;

        try(
                Connection con = DriverManager.getConnection(url, user, password);
                Statement statement = con.createStatement();
                )

        {
            statement.executeUpdate(sql);
        }

    }

    private static void insertData(Customer customer) throws SQLException {
        Connection con = null;

        String sql = "insert into customers values(null,?,?,?,?,?,?,?,?) ";

        PreparedStatement statement = null;

        try {

            con = DriverManager.getConnection(url, user, password);

            statement = con.prepareStatement(sql);


            statement.setString(1, customer.getFirstName() );
            statement.setString(2, customer.getLastName());
            statement.setDate(3, new Date(customer.getBirthDate().getTime()));
            statement.setString(4,customer.getPhone() );
            statement.setString(5, customer.getAddress());
            statement.setString(6, customer.getCity());
            statement.setString(7, customer.getState());
            statement.setInt(8, customer.getPoint());

            statement.executeUpdate();

        } finally {

            try {
                if (statement != null && !statement.isClosed()) statement.close();
            } finally {
                if (con != null && !con.isClosed()) con.close();

            }
        }
    }

    private static void updateTest(Customer customer) throws SQLException {
        Connection con = null;

        String sql = "update customers set  first_name = ? , last_name = ? ,birth_date = ? ," +
                "phone = ? , address = ?,city = ?,  state = ?, points = ? where customer_id =" + customer.getId();

        PreparedStatement statement = null;

        try{

            con = DriverManager.getConnection(url, user, password);

            statement = con.prepareStatement(sql);


            statement.setString(1,"Linda");
            statement.setString(2,"Screen");
            statement.setDate(3,new Date(99,9,9));
            statement.setString(4,"110");
            statement.setString(5,"北极");
            statement.setString(6,"北极星城");
            statement.setString(7,"XY");
            statement.setInt(8,9999);

            statement.executeUpdate();
        }finally {

                try {
                    if (statement != null && !statement.isClosed()) statement.close();
                } finally {
                    if (con != null  && !con.isClosed()) con.close();

                }

        }

    }

    /**
     * 通过预编译，把"' or '1' = '1 "中的 ' 进行转义 ,变成 /' <br/>
     * 由此可以防止SQL注入
     * @param firstName
     * @param lastName
     * @return
     * @throws SQLException
     */
    private static boolean checkLoginPlus(String firstName, String lastName) throws SQLException {
        String sql = "select * from customers where first_name = ? and last_name = ?";
        try {
            // 创建连接
            con = DriverManager.getConnection(url, user, password);
            // 准备SQL

            // 打包  ， 编译
            // st = con.createStatement();

            PreparedStatement statement = con.prepareStatement(sql);

            statement.setString(1, firstName);
            statement.setString(2, lastName);
            // 执行
            // 获取结果
            resultSet = statement.executeQuery();

            // while(resultSet.next()){
            //     int customerId = resultSet.getInt("customer_id");
            //     String firstName1 = resultSet.getString("first_name");
            //
            //     Date birthDate = resultSet.getDate("birth_date");
            //
            //     int points = resultSet.getInt("points");
            //
            //
            //     System.out.println( "顾客ID: " + customerId + "\t顾客姓名: " + firstName1
            //             + "\t\t顾客生日: "  + birthDate + "\t\t顾客积分: " + points);
            // }

            // 处理数据
            // 关闭连接资源

            return resultSet.next();

        } finally {

            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } finally {
                try {
                    if (st != null) st.close();
                } finally {
                    if (con != null) con.close();

                }
            }
        }
    }

    /**
     * 有SQL注入的危险，最好使用预编译<br/>
     * 模拟登录,firstName为用户名，lastName为密码
     * @param firstName
     * @param lastName
     * @return
     * @throws SQLException
     */
    private static boolean checkLogin(String firstName, String lastName) throws SQLException {

        String sql = "select * from customers where first_name = '" + firstName + "' and last_name = '" + lastName + "'";
        try {
            // 创建连接
            con = DriverManager.getConnection(url, user, password);
            // 准备SQL

            // 打包  ， 编译
            st = con.createStatement();

            // 执行
            // 获取结果
            resultSet = st.executeQuery(sql);

            // while(resultSet.next()){
            //     int customerId = resultSet.getInt("customer_id");
            //     String firstName1 = resultSet.getString("first_name");
            //
            //     Date birthDate = resultSet.getDate("birth_date");
            //
            //     int points = resultSet.getInt("points");
            //
            //
            //     System.out.println( "顾客ID: " + customerId + "\t顾客姓名: " + firstName1
            //             + "\t\t顾客生日: "  + birthDate + "\t\t顾客积分: " + points);
            // }

            // 处理数据
            // 关闭连接资源

            return resultSet != null;

        } finally {

            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } finally {
                try {
                    if (st != null) st.close();
                } finally {
                    if (con != null) con.close();

                }
            }
        }

    }

    private static Customer findById(int id) throws Exception {
        String sql = "select * from customers where customer_id = " + id;
        try (
                Connection con = DriverManager.getConnection(url, user, password);
                Statement st = con.createStatement();
                ResultSet resultSet = st.executeQuery(sql);
        ) {

            Customer c = null;
            if (resultSet.next()) {
                // 如果有数据，这个时候，rs会指向一行记录
                // 我们操作数据的时候，只能让指针来读取某一列的信息
                // int customerId = resultSet.getInt("customer_id");


                    c = map.mapping(resultSet);

                // System.out.println(customerId + ":" + firstName + ":" + birthDate + ":" + points);
            }

            return c;
        }
    }

    private static List<Customer> findAll() throws SQLException {

        List<Customer> list = new ArrayList<>();
        try {

            // 创建连接
            con = DriverManager.getConnection(url, user, password);

            // 准备SQL
            String sqlStr = "select * from customers";

            // 打包  ， 编译
            st = con.createStatement();

            // 执行
            // 获取结果
            resultSet = st.executeQuery(sqlStr);

            while (resultSet.next()) {


                list.add(map.mapping(resultSet));

                // System.out.println("顾客ID: " + customerId + "\t顾客姓名: " + firstName
                //         + "\t\t顾客生日: " + birthDate + "\t\t顾客积分: " + points);
            }

            return list;

            // 处理数据
            // 关闭连接资源
        } finally {
            try {
                if (resultSet != null && !resultSet.isClosed()) {
                    resultSet.close();
                }
            } finally {
                try {
                    if (st != null && !st.isClosed()) st.close();
                } finally {
                    if (con != null  && !con.isClosed()) con.close();

                }
            }
        }
    }


}
